﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using CustomsSystem.Model;
using Maticsoft.DBUtility;

namespace CustomsSystem.DAL
{
    public class ScoreService
    {

        /// <summary>
        /// 是否存在评论记录
        /// </summary>
        public bool Exists(int? sheetID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Cu_Comment");
            strSql.Append(" where sheetID=" + sheetID + " ");
            return DbHelperSQL.Exists(strSql.ToString());
        }

        /// <summary>
        /// 增加评论
        /// </summary>
        public int Add(CustomsSystem.Model.Score model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();
            if (model.score != null)
            {
                strSql1.Append("score,");
                strSql2.Append("" + model.score + ",");
            }
            if (model.sheetID != null)
            {
                strSql1.Append("sheetID,");
                strSql2.Append("" + model.sheetID + ",");
            }
            if (model.suggest != null)
            {
                strSql1.Append("suggest,");
                strSql2.Append("'" + model.suggest + "',");
            }
            if (model.handle != null)
            {
                strSql1.Append("handle,");
                strSql2.Append("'" + model.handle + "',");
            }
            strSql.Append("insert into Cu_Comment(");
            strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
            strSql.Append(")");
            return DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public CustomsSystem.Model.Score GetModel(int sheetID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1  ");
            strSql.Append("sheetID,score,suggest,handle ");
            strSql.Append(" from Cu_Comment ");
            strSql.Append(" where sheetID='" + sheetID + "'");
            CustomsSystem.Model.Score model = new CustomsSystem.Model.Score();
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public CustomsSystem.Model.Score DataRowToModel(DataRow row)
        {
            CustomsSystem.Model.Score model = new CustomsSystem.Model.Score();
            if (row != null)
            {
                if (row["sheetID"] != null && row["sheetID"].ToString() != "")
                {
                    model.sheetID = int.Parse(row["sheetID"].ToString());
                }
                if (row["score"] != null && row["score"].ToString() != "")
                {
                    model.score = int.Parse(row["score"].ToString());
                }
                if (row["suggest"] != null && row["suggest"].ToString() != "")
                {
                    model.suggest = row["suggest"].ToString();
                }
                if (row["handle"] != null && row["handle"].ToString() != "")
                {
                    model.handle = row["handle"].ToString();
                }
            }
            return model;
        }

        /// <summary>
        /// 获得评论列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select sheetID,score,suggest ");
            strSql.Append("from Cu_Comment");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append("sheetID,score,suggest,handle ");
            strSql.Append("FROM Cu_Comment");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 增加处理意见
        /// </summary>
        public bool Revise(CustomsSystem.Model.Score model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Cu_Comment set ");
            if (model.handle != null)
            {
                strSql.Append("handle='" + model.handle + "' ");
                strSql.Append("where sheetID=" + model.sheetID + "");
                int rowsAffected = DbHelperSQL.ExecuteSql(strSql.ToString());
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 是否存在处理意见记录
        /// </summary>
        public bool Exists(int? sheetID, string handle)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Cu_Comment");
            strSql.Append(" where sheetID=" + sheetID + " and handle='" + handle + "' ");
            return DbHelperSQL.Exists(strSql.ToString());
        }
    }
}
